-- psql projet_4ir
-- psql -f creationDB.sql projet_4ir
-- \q pour quitter

SET client_min_messages TO WARNING ;

-- ****************************************************************
-- ****************************************************************
-- *************** NETTOYAGE DE LA BASE DE DONNEES ****************
-- ****************************************************************
-- ****************************************************************

DROP TABLE IF EXISTS Absence CASCADE ;
DROP TABLE IF EXISTS Cours CASCADE ;
DROP TABLE IF EXISTS TDs CASCADE ;
DROP TABLE IF EXISTS TPs CASCADE ;
DROP TABLE IF EXISTS Seance CASCADE ;
DROP TABLE IF EXISTS DossierMedical CASCADE ;
DROP TABLE IF EXISTS Affectations CASCADE ;
DROP TABLE IF EXISTS Enseignant CASCADE;
DROP TYPE IF EXISTS Type_Enseignant CASCADE;
DROP TYPE IF EXISTS Type_Statut_Enseignant CASCADE;
DROP TABLE IF EXISTS Departement CASCADE ;
DROP TABLE IF EXISTS Etudiant CASCADE ;
DROP TABLE IF EXISTS Secretaire CASCADE ;
DROP TABLE IF EXISTS Salle CASCADE ;
DROP TYPE IF EXISTS Specialite CASCADE ;
DROP TABLE IF EXISTS Matiere CASCADE ;
DROP TYPE IF EXISTS Type_Seance CASCADE ;
DROP TYPE IF EXISTS Type_Status_Course CASCADE ;
DROP TYPE IF EXISTS Type_Statut_Secretaire CASCADE ;

-- ****************************************************************
-- ****************************************************************
-- ********************* CREATION DES TABLES **********************
-- ****************************************************************
-- ****************************************************************

CREATE TYPE Type_Enseignant AS ENUM ('Ens_Chercheur', 'Ingenieur', 'Ens_Agree');
CREATE TYPE Type_Statut_Enseignant AS ENUM('Permanent', 'Vacataire', 'ATER');

CREATE TABLE Enseignant (
	immatriculation VARCHAR(20) PRIMARY KEY,
	nom VARCHAR(30),
	prenom VARCHAR(30),
	fonction Type_Enseignant NOT NULL,
	statut Type_Statut_Enseignant NOT NULL,
	email VARCHAR(50),
	md5sumpassword VARCHAR(32) --md5sum du passwordw
);

CREATE TYPE Specialite AS ENUM('IR', 'RT', 'AE', 'IS', 'GM'); 
CREATE TYPE Type_Statut_Secretaire AS ENUM('CONTRACTUEL', 'PERMANENT');

CREATE TABLE Departement (
       id_departement INT PRIMARY KEY,
       libelle VARCHAR(10),
       chef VARCHAR(20) REFERENCES Enseignant(immatriculation),
       adjoint VARCHAR(20) REFERENCES Enseignant(immatriculation),
       setSpecialite Specialite[6], -- maximum 6 specialites
       UNIQUE(libelle)
);

CREATE TABLE Affectations (
       departement INT REFERENCES Departement(id_departement),
       enseignant VARCHAR(20) REFERENCES Enseignant(immatriculation),
       PRIMARY KEY(departement, enseignant)
);

CREATE TABLE DossierMedical (
       ine VARCHAR(20) PRIMARY KEY,
       nature_maladie VARCHAR(50),
       gravite INT,
       chronique BOOLEAN
);

CREATE TYPE Type_Status_Course AS ENUM('OPTIONNEL', 'OBLIGATOIRE');
CREATE TYPE Type_Seance AS ENUM('COURS', 'TDS', 'TPS');

CREATE TABLE Matiere ( 
       id_matiere INT PRIMARY KEY,
       nom VARCHAR(50),
       responsable VARCHAR(20) REFERENCES Enseignant(immatriculation),
       nb_heures_Cours INT,
       nb_heures_Tds INT,
       nb_heures_Tps INT
);

CREATE TABLE Salle (
       id_salle INT PRIMARY KEY,
       nom VARCHAR(30),
       nb_places INT,
       departement INT REFERENCES Departement(id_departement)
);

CREATE TABLE Seance (
       id_seance INT,
       matiere INT REFERENCES Matiere(id_matiere),
       isA Type_Seance,
       enseignant VARCHAR(20) REFERENCES Enseignant(immatriculation),
       heure TIMESTAMP,
       salle INT REFERENCES Salle(id_salle),
       PRIMARY KEY(id_seance, matiere, isA)
);

CREATE TABLE Cours (
) INHERITS (Seance);

CREATE TABLE TDs (
) INHERITS (Seance);

CREATE TABLE TPs (
) INHERITS (Seance);

CREATE TABLE Etudiant (
     ine VARCHAR(20) PRIMARY KEY,
     nom VARCHAR(30),
     prenom VARCHAR(30),
     birthdate DATE,
     nationalite VARCHAR(20),
     affectation Specialite,
     setCourses INTEGER[20],
     statusCourses Type_Status_Course[20],
     email VARCHAR(50),
     md5sumpassword VARCHAR(32) --md5sum du password
);

CREATE TABLE Secretaire ( 
	id_secretaire INT PRIMARY KEY,
	nom VARCHAR(30),
	prenom VARCHAR(30),
	date_embauche DATE,
	statut Type_Statut_Secretaire,
	departement INT REFERENCES Departement(id_departement),
	email VARCHAR(50),
	md5sumpassword VARCHAR(32) --md5sum du password
);

CREATE TABLE Absence (
       id_seance INT,
       matiere INT REFERENCES Matiere(id_matiere),
       isA Type_Seance,
       ine VARCHAR(20) REFERENCES Etudiant(ine),
       justification VARCHAR(300),
       credibilite INT,
       PRIMARY KEY(id_seance, matiere, isA, ine)
);

-- ****************************************************************
-- ****************************************************************
-- ************* CREATION DES FONCTIONS D'INSERTION ***************
-- ****************************************************************
-- ****************************************************************

-- AJOUT D'ENSEIGNANT
DROP FUNCTION IF EXISTS addEnseignant(VARCHAR(20), VARCHAR(30), VARCHAR(30), Type_Enseignant, Type_Statut_Enseignant, VARCHAR(50), VARCHAR(32));

CREATE OR REPLACE FUNCTION addEnseignant(immatriculation VARCHAR(20), nom VARCHAR(30), prenom VARCHAR(30), fonction Type_Enseignant, statut Type_Statut_Enseignant, email VARCHAR(50), md5sumpassword VARCHAR(32)) RETURNS void AS $$
BEGIN
	INSERT INTO Enseignant VALUES (immatriculation, nom, prenom, fonction, statut, email, md5sumpassword);
END;
$$ LANGUAGE plpgsql;

--AJOUT DE DEPARTEMENT
DROP FUNCTION IF EXISTS addDepartement(INT, VARCHAR(10), VARCHAR(20), adjoint VARCHAR(20), Specialite[6]);

CREATE OR REPLACE FUNCTION addDepartement(id_departement INT, libelle VARCHAR(10), chef VARCHAR(20), adjoint VARCHAR(20), setSpecialite Specialite[6]) RETURNS void AS $$
BEGIN
	INSERT INTO Departement VALUES (id_departement, libelle, chef, adjoint, setSpecialite);
END;
$$ LANGUAGE plpgsql;

--AJOUT DE SALLES
DROP FUNCTION IF EXISTS addSalle(INT, VARCHAR(30), INT, INT);

CREATE OR REPLACE FUNCTION addSalle(id_salle INT, nom VARCHAR(30), nb_places INT, departement INT) RETURNS void AS $$
BEGIN
	INSERT INTO Salle VALUES (id_salle, nom, nb_places, departement);
END;
$$ LANGUAGE plpgsql;

--AJOUT D'AFFECTATIONS
DROP FUNCTION IF EXISTS addAffectation(INT, VARCHAR(20));

CREATE OR REPLACE FUNCTION addAffectation(departement INT, enseignant VARCHAR(20)) RETURNS void AS $$
BEGIN
	INSERT INTO Affectations VALUES (departement, enseignant) ;
END;
$$ LANGUAGE plpgsql;

--AJOUT D'ETUDIANTS
DROP FUNCTION IF EXISTS addEtudiant(VARCHAR(20), VARCHAR(30), VARCHAR(30), DATE, VARCHAR(20), Specialite, INTEGER[20], Type_Status_Course[20], VARCHAR(50), VARCHAR(32));

CREATE OR REPLACE FUNCTION addEtudiant(ine VARCHAR(20), nom VARCHAR(30), prenom VARCHAR(30), birthdate DATE, nationalite VARCHAR(20), affectation Specialite, setCourses INTEGER[20], statusCourses Type_Status_Course[20], email VARCHAR(50), md5sumpassword VARCHAR(32)) RETURNS void AS $$
BEGIN
	INSERT INTO Etudiant VALUES(ine, nom, prenom, birthdate, nationalite, affectation, setCourses, statusCourses, email, md5sumpassword);
END;
$$ LANGUAGE plpgsql;

--AJOUT DES DOSSIERS MEDICAUX
DROP FUNCTION IF EXISTS addDossierMedical(VARCHAR(20), VARCHAR(50), INT, BOOLEAN);

CREATE OR REPLACE FUNCTION addDossierMedical(ine VARCHAR(20), nature_maladie VARCHAR(50), gravite INT, chronique BOOLEAN) RETURNS void AS $$
BEGIN
	INSERT INTO DossierMedical VALUES (ine, nature_maladie, gravite, chronique);
END;
$$ LANGUAGE plpgsql;

--AJOUT DES SECRETAIRES
DROP FUNCTION IF EXISTS addSecretaire( INT, VARCHAR(30), VARCHAR(30), DATE, Type_Statut_Secretaire, INT, VARCHAR(50), VARCHAR(32) );

CREATE OR REPLACE FUNCTION addSecretaire(id_secretaire INT, nom VARCHAR(30), prenom VARCHAR(30), date_embauche DATE, statut Type_Statut_Secretaire, departement INT, email VARCHAR(50), md5sumpassword VARCHAR(32)) RETURNS void AS $$
BEGIN
	INSERT INTO Secretaire VALUES (id_secretaire, nom, prenom, date_embauche, statut, departement, email, md5sumpassword);
END;
$$ LANGUAGE plpgsql;

--AJOUT DES MATIERES
DROP FUNCTION IF EXISTS addMatiere(INT, VARCHAR(50), VARCHAR(20), INT, INT, INT);

CREATE OR REPLACE FUNCTION addMatiere(id_matiere INT, nom VARCHAR(50), responsable VARCHAR(20), nb_heures_Cours INT, nb_heures_Tds INT, nb_heures_Tps INT) RETURNS void AS $$
BEGIN
	INSERT INTO Matiere VALUES (id_matiere, nom, responsable, nb_heures_Cours, nb_heures_Tds, nb_heures_Tps);
END;
$$ LANGUAGE plpgsql;

--AJOUT DES SEANCES
DROP FUNCTION IF EXISTS addCours(INT, INT, VARCHAR(20), INT, TIMESTAMP);
DROP FUNCTION IF EXISTS addTPs(INT, INT, VARCHAR(20), INT, TIMESTAMP);
DROP FUNCTION IF EXISTS addTDs(INT, INT, VARCHAR(20), INT, TIMESTAMP);

CREATE OR REPLACE FUNCTION addCours(id_seance INT, matiere INT, enseignant VARCHAR(20), salle INT, heure TIMESTAMP) RETURNS void AS $$
BEGIN
	INSERT INTO Cours VALUES (id_seance, matiere, 'COURS', enseignant, heure, salle);
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION addTDs(id_seance INT, matiere INT, enseignant VARCHAR(20), salle INT, heure TIMESTAMP) RETURNS void AS $$
BEGIN
	INSERT INTO TDs VALUES (id_seance, matiere, 'TDS', enseignant, heure, salle);
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION addTPs(id_seance INT, matiere INT, enseignant VARCHAR(20), salle INT, heure TIMESTAMP) RETURNS void AS $$
BEGIN
	INSERT INTO TPs VALUES (id_seance, matiere, 'TPS', enseignant, heure, salle);
END;
$$ LANGUAGE plpgsql;

--AJOUT DES ABSENCES
DROP FUNCTION IF EXISTS addAbsent(INT, INT, Type_Seance, VARCHAR(20), VARCHAR(100), INT);

CREATE OR REPLACE FUNCTION addAbsent(id_seance INT, matiere INT, isA Type_Seance, ine VARCHAR(20), justification VARCHAR(100), credibilite INT) RETURNS void AS $$
BEGIN
	INSERT INTO Absence VALUES (id_seance, matiere, isA, ine, justification, credibilite);
END;
$$ LANGUAGE plpgsql;